package com.wind.utils.file;

import com.wind.utils.IOUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;


/**
 * <p>
 *      excel工具类
 * </p>
 * @author wind
 * @date    2024/12/11 14:59
 * @version v1.0
 */
public class ExcelUtil {

    private ExcelUtil(){

    }

    /**
     * 写入文件
     * @param path 文件路径
     * @param list
     */
    public static void write(String path, List<List<String>> list){
        OutputStream out = null;
        Workbook wb = getWorkbook(path, true);
        try {
            out = Files.newOutputStream(Paths.get(path));
            Sheet sheet = wb.createSheet();
            putSheet(sheet, list);
            wb.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            IOUtil.close(out);
        }
    }


    /**
     * 读取excel指定页的数据
     * @param path
     * @return
     */
    public static List<List<String>> read(String path) {
        return read(path, 0, false);
    }

    /**
     * 读取excel指定页的数据
     * @param path
     * @return
     */
    public static List<List<String>> read(String path, boolean endEmpty) {
        return read(path, 0, endEmpty);
    }

    /**
     * 读取excel指定页的数据
     * @param path
     * @param sheetNum
     * @return
     */
    public static List<List<String>> read(String path, int sheetNum, boolean endEmpty) {
        List<List<String>> listSheet = new ArrayList<>();
        Workbook wb = getWorkbook(path, false);
        int sheets = wb.getNumberOfSheets();
        if(sheetNum <= sheets && sheetNum >=0){
            //获取sheet
            Sheet sheet = wb.getSheetAt(sheetNum);
            listSheet = getSheet(sheet, endEmpty);
        }
        return listSheet;
    }


    /**
     * 获取指定页sheet的数据
     * @param sheet
     * @return
     */
    private static List<List<String>> getSheet(Sheet sheet, boolean endEmpty){
        List<List<String>> list = new ArrayList<>();
        // 获得表单的迭代器
        Iterator<Row> rows = sheet.rowIterator();
        int count = 0;
        List<String> headerList = new ArrayList<>();
        while (rows.hasNext()) {
            count++;
            // 获得行数据
            Row row = rows.next();
            if(count == 1){
                headerList.addAll(getRow(row, endEmpty));
                list.add(headerList);
                continue;
            }
            list.add(getRow(row, headerList.size()));
        }
        return list;
    }

    /**
     * 读取一行数据, 列数不够，空字符串补齐
     * @param row
     * @param colSize 列数
     * @return
     */
    public static List<String> getRow(Row row, int colSize){
        List<String> rows = new ArrayList<>();
        Iterator<Cell> cells = row.cellIterator();
        for(int i = 0; i < colSize; i++){
            String value = "";
            if(cells.hasNext()){
                Cell cell = cells.next();
                value = getCellValue(cell);
            }
            rows.add(value);
        }
        return rows;
    }

    /**
     * 读取一行数据
     * @param row
     * @param endEmpty 是否以空列结束
     * @return
     */
    public static List<String> getRow(Row row, boolean endEmpty){
        List<String> rows = new ArrayList<>();
        Iterator<Cell> cells = row.cellIterator();
        while (cells.hasNext()) {
            Cell cell = cells.next();
            String value = getCellValue(cell);
            if("".equals(value.trim()) && endEmpty){
                break;
            }
            rows.add(value);
        }
        return rows;
    }

    /**
     * 获取单元格的值
     * @param cell
     * @return
     */
    public static String getCellValue(Cell cell){
        String value = "";
        if(cell == null){
            return value;
        }
        CellType cellType = cell.getCellType();
        if(CellType.FORMULA.equals(cellType)){
            value = "" + cell.getCellFormula();
        }else if(CellType.NUMERIC.equals(cellType)){
            value = "" + cell.getNumericCellValue();
        }else if(CellType.STRING.equals(cellType)){
            value = "" + cell.getStringCellValue();
        }else if(CellType.BLANK.equals(cellType)){
            value = "";
        }else if(CellType.BOOLEAN.equals(cellType)){
            value = "" + cell.getBooleanCellValue();
        }else if(CellType.ERROR.equals(cellType)){
            value = "";
        }
        return value;
    }

    /**
     * 向指定页中写入数据
     * @param sheet
     * @param list
     */
    private static void putSheet(Sheet sheet, List<List<String>> list){
        Row row;
        Cell c;
        if(sheet != null && list != null){
            for(int i = 0; i < list.size(); i++){
                row =  sheet.createRow(i);
                List<String> rowList = list.get(i);
                for(int j = 0; j < rowList.size(); j++){
                    String value = rowList.get(j);
                    c = row.createCell(j);
                    c.setCellValue(value);
                }
            }
        }
    }

    /**
     * 获取工作簿workbook
     * @param path
     * @param isCreate true表示新建 false表示读取
     * @return
     */
    private static Workbook getWorkbook(String path, boolean isCreate){
        Workbook wb = null;
        InputStream input = null;
        try {
            if(isCreate){
                if(path.endsWith("xls")){
                    //2003-2007
                    wb = new HSSFWorkbook();
                }else{
                    //2007+
                    wb = new XSSFWorkbook();
                }
            }else{
                input = Files.newInputStream(Paths.get(path));
                if(path.endsWith("xlsx")){
                    //2007+
                    wb = new XSSFWorkbook(input);
                }else{
                    //2003-2007
                    wb = new HSSFWorkbook(input);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally{
            IOUtil.close(input);
        }
        return wb;
    }
}
